﻿'========================================================================
'功能:網頁名稱及相關設定資料庫存取物件
'作者:andy
'日期: 2011/1/28
'========================================================================

Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Text
Imports System.Data
Imports MySql.Data.MySqlClient


Namespace OAMS.DAO
    Public Class MenuDAO
        Inherits BaseDAO

        Dim _dbName As String
        Public Sub New(ByVal dbName As String)
            _dbName = dbName
        End Sub

        ''' <summary>
        ''' 新增一筆Menu資料
        ''' </summary>
        ''' <param name="RootName"></param>
        ''' <param name="Name"></param>
        ''' <param name="NeedControl"></param>
        ''' <param name="URL"></param>
        ''' <param name="GroupID"></param>
        ''' <param name="UID"></param>
        ''' <returns>新增一筆Menu資料</returns>
        ''' <remarks>新增一筆Menu資料</remarks>
        Public Function INSERT_NewOne(ByVal RootName As String, ByVal Name As String, ByVal NeedControl As Boolean, ByVal URL As String, ByVal GroupID As String, ByVal UID As String) As Integer
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()
                Dim sb As New StringBuilder()
                sb.AppendLine(" select max(ProgramID)+1 as MAX1 from Menu ")

                Dim oTable As Data.DataTable
                Dim cmd As MySqlCommand
                cmd = New MySqlCommand(sb.ToString(), con)
                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                Dim max As Integer
                If oTable.Rows(0)("MAX1").ToString <> "" Then
                    max = Integer.Parse(oTable.Rows(0)("MAX1").ToString)
                Else
                    max = 1
                End If
                sb.Length = 0
                sb.AppendLine("INSERT INTO Menu  ( ProgramID,RootName, Name, NeedControl, URL, GroupID, CrtUID,ChnName,EngName,CrtDate,ModiUID,isMenu,OrderByID,QueryString) VALUES (")
                sb.AppendLine("?ProgramID, ?RootName,?Name, ?NeedControl, ?URL, ?GroupID, ?UID,'','',?aData,'',1,'','') ")
                cmd = New MySqlCommand(sb.ToString(), con)
                cmd.Parameters.AddWithValue("?ProgramID", max)
                cmd.Parameters.AddWithValue("?RootName", RootName)
                cmd.Parameters.AddWithValue("?Name", Name)
                cmd.Parameters.AddWithValue("?NeedControl", NeedControl)
                cmd.Parameters.AddWithValue("?URL", URL)
                cmd.Parameters.AddWithValue("?GroupID", GroupID)
                cmd.Parameters.AddWithValue("?UID", UID)
                cmd.Parameters.AddWithValue("?aData", DateTime.Now)
                cmd.ExecuteNonQuery()



                Return max
            Catch ex As Exception
            Finally
                con.Close()

            End Try
        End Function

        ''' <summary>
        ''' 取得有授權的Menu資料
        ''' </summary>
        ''' <param name="UID"></param>
        ''' <returns>取得有授權的Menu資料</returns>
        ''' <remarks>取得有授權的Menu資料</remarks>
        Public Function GetMenuUID(ByVal UID As String) As DataTable
            Dim sb As New StringBuilder()
            sb.AppendLine(" Select * from Menu where GroupID in (select GroupID from GroupMember where UID=?UID) order by OrderByID")
            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()
                cmd = New MySqlCommand(sb.ToString(), con)
               
                cmd.Parameters.AddWithValue("?UID", UID)


                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)

               
                Return oTable


            Catch ex As Exception
            Finally
                con.Close()

            End Try

        End Function

        ''' <summary>
        ''' 取得畫面檔資料
        ''' </summary>
        ''' <returns>取得畫面檔資料</returns>
        ''' <remarks>取得畫面檔資料</remarks>
        Public Function GetMenu() As DataTable
           Dim sb As New StringBuilder()
            sb.AppendLine("Select ProgramID,RootName,Name,NeedControl,URL,GroupID  ,QueryString, ChnName,EngName ,IsMenu from Menu  order by ProgramID desc")
            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()
                cmd = New MySqlCommand(sb.ToString(), con)
                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                Return oTable
            Catch ex As Exception
            Finally
                con.Close()
            End Try


        End Function

        ''' <summary>
        ''' 修改Memu資料
        ''' </summary>
        ''' <param name="ProgramID"></param>
        ''' <param name="RootName"></param>
        ''' <param name="Name"></param>
        ''' <param name="NeedControl"></param>
        ''' <param name="URL"></param>
        ''' <param name="GroupID"></param>
        ''' <param name="UID"></param>
        ''' <remarks>修改Memu資料</remarks>
        Public Sub UpdateMenu(ByVal ProgramID As Integer, ByVal RootName As String, ByVal Name As String, ByVal NeedControl As Boolean, ByVal URL As String, ByVal GroupID As String, ByVal UID As String, ByVal QueryString As Boolean, ByVal ChnName As String, ByVal EngName As String, ByVal IsMenu As Boolean)


            Dim sb As New StringBuilder()
            sb.AppendLine("update Menu  set RootName=?RootName, Name=?Name,ChnName=?ChnName,EngName =?EngName,NeedControl=?NeedControl,URL=?URL , GroupID=?GroupID ,ModiUID=?UID ,ModiDate=?aDate,QueryString=?QueryString,IsMenu=?IsMenu where ProgramID=?ProgramID")
            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try
                con.Open()
                cmd = New MySqlCommand(sb.ToString, con)
                cmd.Parameters.AddWithValue("?RootName", RootName)
                cmd.Parameters.AddWithValue("?Name", Name)
                cmd.Parameters.AddWithValue("?ChnName", ChnName)
                cmd.Parameters.AddWithValue("?EngName", EngName)
                cmd.Parameters.AddWithValue("?NeedControl", NeedControl)
                cmd.Parameters.AddWithValue("?URL", URL)
                cmd.Parameters.AddWithValue("?GroupID", GroupID)
                cmd.Parameters.AddWithValue("?UID", UID)
                cmd.Parameters.AddWithValue("?aDate", DateTime.Now)
                cmd.Parameters.AddWithValue("?QueryString", QueryString)
                cmd.Parameters.AddWithValue("?IsMenu", IsMenu)
                cmd.Parameters.AddWithValue("?ProgramID", ProgramID)
                cmd.ExecuteNonQuery()
            Catch ex As Exception
            Finally
                con.Close()
            End Try


        End Sub

        ''' <summary>
        ''' 取得某一畫面權限
        ''' </summary>
        ''' <param name="ProgramID"></param>
        ''' <returns>取得某一畫面權限</returns>
        ''' <remarks>取得某一畫面權限</remarks>
        Public Function GetMenuRights(ByVal ProgramID As Integer) As DataTable
           

            Dim sb As New StringBuilder()
            sb.AppendLine("Select SeqNo, DisplayName ,0  as canModi from MenuRights where ProgramID=?ProgramID  union select 0 as SeqNo,'' as Display ,1 as canModi order by SeqNo")
            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()
                cmd = New MySqlCommand(sb.ToString(), con)
                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                cmd.Parameters.AddWithValue("?ProgramID", ProgramID)

                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                Return oTable
            Catch ex As Exception
            Finally
                con.Close()
            End Try

        End Function

        ''' <summary>
        ''' 新增或修改一筆MenuRights資料
        ''' </summary>
        ''' <param name="ProgramID"></param>
        ''' <param name="SeqNo"></param>
        ''' <param name="DisplayName"></param>
        ''' <param name="UID"></param>
        ''' <remarks>新增或修改一筆MenuRights資料</remarks>
        Public Sub UpdateMenuRights(ByVal ProgramID As Integer, ByVal SeqNo As Integer, ByVal DisplayName As String, ByVal UID As String)
            'Dim sqlParams As SqlParameter() = New SqlParameter(4) {}
            'sqlParams(0) = New SqlParameter("@ProgramID", SqlDbType.Int)
            'sqlParams(0).Value = ProgramID
            'sqlParams(1) = New SqlParameter("@SeqNo", SqlDbType.Int)
            'sqlParams(1).Value = SeqNo
            'sqlParams(2) = New SqlParameter("@DisplayName", SqlDbType.NVarChar)
            'sqlParams(2).Value = DisplayName
            'sqlParams(3) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            'sqlParams(3).Value = UID

            'Dim sb As New StringBuilder()
            'If SeqNo = 0 Then
            '    sb.AppendLine("declare @SeqNo1 int select @SeqNo1=max(SeqNo)+1 from MenuRights where ProgramID=@ProgramID insert into MenuRights (ProgramID,SeqNo,DisplayName,CrtUID,CrtDate,ModiUID,ModiDate)")
            '    sb.AppendLine("values(@ProgramID,isnull(@SeqNo1,1),@DisplayName,@UID,getdate(),'',null)")
            '    sb.AppendLine(" select AA=@SeqNo1")
            'Else
            '    sb.AppendLine("update  MenuRights  set DisplayName=@DisplayName,ModiUID=@UID,ModiDate=GetDate()")
            '    sb.AppendLine("where ProgramID= @ProgramID and SeqNo=@SeqNo")

            'End If

            'SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)



            Dim sb As New StringBuilder()
            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try
                If SeqNo = 0 Then
                    sb.AppendLine("select max(SeqNo)+1 as MAX1  from MenuRights where ProgramID=?ProgramID ")
                    con.Open()
                    cmd = New MySqlCommand(sb.ToString, con)
                    Dim oDbAdapter As MySqlDataAdapter
                    oDbAdapter = New MySqlDataAdapter(cmd)
                    cmd.Parameters.AddWithValue("?ProgramID", ProgramID)

                    Dim oTable As Data.DataTable
                    oTable = New Data.DataTable()
                    oDbAdapter.Fill(oTable)
                    Dim max As Integer
                    If oTable.Rows(0)("MAX1").ToString <> "" Then
                        max = Integer.Parse(oTable.Rows(0)("MAX1").ToString)
                    Else
                        max = 1
                    End If




                    sb.Length = 0
                    sb.AppendLine("insert into MenuRights (ProgramID,SeqNo,DisplayName,CrtUID,CrtDate,ModiUID,ModiDate)")
                    sb.AppendLine("values(?ProgramID,?SeqNo,?DisplayName,?UID,?adate,'',null)")
                    
                    cmd = New MySqlCommand(sb.ToString, con)
                    cmd.Parameters.AddWithValue("?ProgramID", ProgramID)
                    cmd.Parameters.AddWithValue("?SeqNo", max)
                    cmd.Parameters.AddWithValue("?DisplayName", DisplayName)
                    cmd.Parameters.AddWithValue("?UID", UID)
                    cmd.Parameters.AddWithValue("?adate", DateTime.Now)
                    cmd.ExecuteNonQuery()
                Else
                    sb.AppendLine("update  MenuRights  set DisplayName=?DisplayName,ModiUID=?UID,ModiDate=?aDate")
                    sb.AppendLine("where ProgramID= ?ProgramID and SeqNo=?SeqNo")
                    con.Open()
                    cmd = New MySqlCommand(sb.ToString, con)
                    cmd.Parameters.AddWithValue("?DisplayName", DisplayName)
                    cmd.Parameters.AddWithValue("?UID", UID)
                    cmd.Parameters.AddWithValue("?adate", DateTime.Now)
                    cmd.Parameters.AddWithValue("?ProgramID", ProgramID)
                    cmd.Parameters.AddWithValue("?SeqNo", SeqNo)
                    cmd.ExecuteNonQuery()
                End If
            Catch ex As Exception
            Finally
                con.Close()
            End Try
        End Sub

    End Class


End Namespace


